/**
 * 
 */
package org.gov.vis.master.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.gov.vis.master.dto.DeptDto;
import org.gov.vis.util.DBConstants;
import org.gov.vis.util.DBStatements;
import org.gov.vis.util.Util;

/**
 * @author Vishwanath
 *
 */
public class DepartmentDaoImpl implements DepartmentDao {

	/**
	 *Method called to either Add or Edit a Department
	 */
	@Override
	public void addOrEditDept(final DeptDto _deptDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			if(_deptDto.getDeptId() > 0){
				pStmt = conn.prepareStatement(DBStatements.EDIT_DEPARTMENT);
				pStmt.setString(1, _deptDto.getCode());
				pStmt.setString(2, _deptDto.getInstitution());
				pStmt.setString(3, _deptDto.getVillage());
				pStmt.setString(4, _deptDto.getMandal());
				pStmt.setString(5, _deptDto.getDistrict());
				pStmt.setLong(6, _deptDto.getDeptId());
			}
			else{
				pStmt = conn.prepareStatement(DBStatements.ADD_DEPARTMENT);
				pStmt.setString(1, _deptDto.getCode());
				pStmt.setString(2, _deptDto.getInstitution());
				pStmt.setString(3, _deptDto.getVillage());
				pStmt.setString(4, _deptDto.getMandal());
				pStmt.setString(5, _deptDto.getDistrict());
			}

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}
	}//end addOrEditDept

	/**
	 *Method called to Delete a Department
	 */
	@Override
	public void deleteDept(final long _deptId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_DEPARTMENT);
			pStmt.setLong(1, _deptId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

	/**
	 *Method called to get the list of Departments based on the passed Department 
	 */
	@Override
	public List<DeptDto> getDepartments(final DeptDto _deptDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<DeptDto> _deptList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_DEPARTMENTS);
			if(_deptDto.getDeptId() > 0){
				pStmt.setLong(1, _deptDto.getDeptId());
				pStmt.setLong(2, _deptDto.getDeptId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			
			if(_deptDto.getCode() != null && !_deptDto.getCode().trim().equals("")){
				pStmt.setString(3, _deptDto.getCode());
			}
			else{
				pStmt.setString(3, "%");
			}
			
			rsSet = pStmt.executeQuery();
			
			_deptList = populateDepartments(rsSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _deptList;
	}//end getDepartments


	/**
	 * Method called to populate the Department records based on the passed result set
	 * @param _rsSet
	 * @return
	 */
	private List<DeptDto> populateDepartments(final ResultSet _rsSet)throws Exception{
		List<DeptDto> _deptList = null;
		DeptDto _deptDto = null;
		
		while(_rsSet.next()){
			if(_rsSet.isFirst()){
				_deptList = new ArrayList<DeptDto>();
			}
			//initialize the Department
			_deptDto = new DeptDto();
			_deptDto.setDeptId(_rsSet.getLong(DBConstants.COL_DEPARTMENT_ID));
			_deptDto.setCode(_rsSet.getString(DBConstants.COL_DEPARTMENT_CODE));
			_deptDto.setInstitution(_rsSet.getString(DBConstants.COL_DEPARTMENT_INST));
			_deptDto.setVillage(_rsSet.getString(DBConstants.COL_DEPARTMENT_VLG));
			_deptDto.setMandal(_rsSet.getString(DBConstants.COL_DEPARTMENT_MDL));
			_deptDto.setDistrict(_rsSet.getString(DBConstants.COL_DEPARTMENT_DIST));
			
			//add the current Object to the list
			_deptList.add(_deptDto);
			
		}
		return _deptList;
	}//end populateDepartments

	/**
	 * Method is called to search the Departments based on the department.
	 */
	@Override
	public List<DeptDto> searchDepartments(final DeptDto _deptDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<DeptDto> _deptList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_DEPARTMENTS);
			if(_deptDto.getDeptId() > 0){
				pStmt.setLong(1, _deptDto.getDeptId());
				pStmt.setLong(2, _deptDto.getDeptId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			
			if(_deptDto.getCode() != null && !_deptDto.getCode().trim().equals("")){
				pStmt.setString(3, _deptDto.getCode() + "%");
			}
			else{
				pStmt.setString(3, "%");
			}
			
			rsSet = pStmt.executeQuery();
			
			_deptList = populateDepartments(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _deptList;
	}//end searchDepartments

}//end class
